package cn.edu.nju.ws.sview.views;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.edu.nju.ws.sview.database.DBConnectionFactory;

import com.hp.hpl.jena.vocabulary.RDF;

public class Bookmark {
	private String name = null;
	private String lang = null;
	private String author = null;
	private int etype;
	private int eid ;
	
	public Bookmark(String name,String author,String lang,int etype,int eid) {
		this.name = name;
		this.author = author;
		this.lang = lang;
		this.etype = etype;
		this.eid = eid;
	}
	
	public void setName(String name) {
		this.name = name;
	}
	
	public void setReference(int etype,int eid) {
		this.etype = etype;
		this.eid = eid;
	}
	
	public void setLang(String lang) {
		this.lang = lang;
	}
	
	public void setAuthor(String author) {
		this.author = author;
	}
	public static ArrayList<Integer> getBookmarkIdsbyUserId(int uid){
		ArrayList<Integer> bmList = new ArrayList<Integer>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("select id from bookmark where user_id=?");
			pstmt.setInt(1, uid);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				bmList.add(rs.getInt(1));
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bmList;
	}
	/**
	 * get the name of bookmark
	 * @param id. the bookmark id
	 * @return null if invalid bookmark id
	 */
	public static String getNameByBookmarkID(int id) {
		String name = null;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(String.format(
					"select name " +
					"from bookmark " +
					"where id = %d", id));
			if ( rs.next() ) {
				name = rs.getString("name") ;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}

	/**
	 * get reference entity type
	 * @return
	 */
	public int getReferenceType() {
		return this.etype;
	}
	
	/**
	 * get reference entity id
	 * @return
	 */
	public int getReferenceID(){
		return this.eid;
	}
	
	/**
	 * get the bookmark's reference entity type and id
	 * @param bookmarkId. the bookmark id
	 * @return. the bookmark's reference entity. First element:type,Second element:id.
	 */
	public static ArrayList<Integer> getMembersByBookmarkId(int bookmarkId){
		ArrayList<Integer> result = new ArrayList<Integer>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(String.format(
					"select entity_id,entity_type " +
					"from bm_entity " +
					"where bm_id = %d", bookmarkId));
			if( rs.next() ) {
				result.add(rs.getInt("entity_type"));
				result.add(rs.getInt("entity_id"));
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * 得到收藏特定成员的bookmark id
	 * @param etype
	 * @param eid
	 * @return 如果不存在返回0
	 */
	public static int hasBookmarkOnMemberLang(int userID,int etype,int eid,String lang){
	    int result = 0;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(String.format(
					"select bm_id from bm_entity,bookmark " +
					"where bm_id = id and entity_type=%d and entity_id = %d and user_id=%d and lang='%s'", etype,eid,userID,lang));
			if( rs.next() ) {
				result = rs.getInt("bm_id");
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	/**
	 * get bookmark id by name with author
	 * @param name. the bookmark name
	 * @param author. the author name
	 * @return. -1 if the author has no bookmark with the name
	 */
	public static int getBookmarkIdByNameAuthor(String name, String author) {
		int bookmarkId = -1;
		int uid = User.getUserIdByName(author);
		if ( uid < 0 ) return -1;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement stmt = conn.prepareStatement("select id from bookmark where user_id =? and name = ? ");
			stmt.setInt(1, uid);
			stmt.setString(2, name);
			ResultSet rs = stmt.executeQuery();
			if ( rs.next() ) {
				bookmarkId = rs.getInt("id");
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bookmarkId;
	}

	/**
	 * get bookmark id by name with author
	 * @param name. the bookmark name
	 * @param authorID. the author ID
	 * @return. -1 if the author has no bookmark with the name
	 */	
	public static int getBookmarkIdByNameAuthor(String name, int authorID) {
		int bookmarkId = -1;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement stmt = conn.prepareStatement("select id from bookmark where where user_id =? and name =?");
			stmt.setInt(1, authorID);
			stmt.setString(2, name);
			ResultSet rs = stmt.executeQuery();
			if ( rs.next() ) {
				bookmarkId = rs.getInt("id");
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bookmarkId;
	}
	
	public String getAuthor() {
		return author;
	}
	
	public String getLang() {
		return lang;
	}
	
    /**
     * get the bookmark's lang
     * @param bookmarkId. The bookmark id
     * @return the lang of bookmark
     */
	public static String getLangByBookmarkId(int bookmarkId){
		String lang = null;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement stmt = conn.prepareStatement("select lang from bookmark where id=?");
			stmt.setInt(1, bookmarkId) ;
			ResultSet rs = stmt.executeQuery();
			if ( rs.next() ) {
				lang = rs.getString("lang");
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lang;
	}
	
	
	/**
	 * rename the bookmark
	 * @param bookmarkId. The bookmark id
	 * @param newName. The new name
	 * @param authorID. The author id
	 * @return. If names have conflict or operation failure, return false;
	 */
	public static boolean rename(int bookmarkId,String newName,int authorID) {
		int checkId = getBookmarkIdByNameAuthor(newName, authorID);
		if ( checkId >= 0 && checkId!=bookmarkId) return false;
		if(checkId==bookmarkId) return true;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement stmt = conn.prepareStatement("update bookmark set name=? where id=?") ;
			stmt.setString(1, newName) ;
			stmt.setInt(2, bookmarkId) ;
			stmt.executeUpdate();
			stmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * serialize the bookmark, if the bookmark name has existed, then merge the members
	 * @return false if the author has bookmark with the same name
	 */
	public boolean serialize(){
		int uid = User.getUserIdByName(author);
		if ( uid < 0 ) return false;
		boolean flag = false;
		int bookmarkId = getBookmarkIdByNameAuthor(name, author);
		if(bookmarkId>=0)
			flag = true;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			if (!flag) {

				PreparedStatement stmt = conn.prepareStatement("insert into bookmark(user_id, lang, name) values(?,?,?)");
				stmt.setInt(1,uid);
				stmt.setString(2, lang);
				stmt.setString(3, name);
				stmt.executeUpdate();
				stmt.close();

				bookmarkId = getBookmarkIdByNameAuthor(name, author);
			}
			PreparedStatement pstmt = conn.prepareStatement(
					"insert into bm_entity(bm_id, entity_type,entity_id) " +
					"values(?, ?,?) on duplicate key update entity_type=values(entity_type),entity_id=values(entity_id)");
			pstmt.setInt(1, bookmarkId);
			pstmt.setInt(2, this.etype);
			pstmt.setInt(3, this.eid);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 表示系统自动serialize the bookmark, if the bookmark name has existed, then merge the members
	 * @return false if the author has bookmark with the same name
	 */
	public boolean serializeByAuto(){
		int uid = User.getUserIdByName(author);
		if ( uid < 0 ) return false;
		boolean flag = false;
		int bookmarkId = getBookmarkIdByNameAuthor(name, author);
		if(bookmarkId>=0)
			flag = true;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			if (!flag) {

                PreparedStatement stmt = conn.prepareStatement("insert into bookmark(user_id, lang, name,isauto) values(?,?,?,?)");
                stmt.setInt(1,uid);
                stmt.setString(2,lang);
                stmt.setString(3, name);
                stmt.setInt(4, 1);
                stmt.executeUpdate();
				stmt.close();

				bookmarkId = getBookmarkIdByNameAuthor(name, author);
			}
			PreparedStatement pstmt = conn.prepareStatement(
					"insert into bm_entity(bm_id, entity_type,entity_id) " +
					"values(?, ?,?) on duplicate key update entity_type=values(entity_type),entity_id=values(entity_id)");
			pstmt.setInt(1, bookmarkId);
			pstmt.setInt(2, this.etype);
			pstmt.setInt(3, this.eid);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 删除与一个bookmark相关的信息
	 * @param bookmarkID
	 */
	public static void delete(int bookmarkID){
		Connection connection = DBConnectionFactory.getConnection();
		
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(String.format("delete from bookmark where id=%d",bookmarkID));
			stmt.executeUpdate(String.format("delete from bm_entity where bm_id=%d",bookmarkID));
			stmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
	Connection connection = DBConnectionFactory.getConnection();
		
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select id from bookmark");
			while(rs.next()){
				int bmid = rs.getInt("id");
				Bookmark.delete(bmid);
			}
			rs.close();
			stmt.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
